import pymysql
from DBUtils.PooledDB import PooledDB

"""
数据库配置文件
"""
DB_USER = 'root'
DB_PASSWORD = 'summit-1'
DB_DATABASE = 'mydb'
DB_PORT = 3306
DB_HOST = '127.0.0.1'
DB_CHARSET = 'utf8'
# 定义需要导入文件路径
XLSX_FILE = r'D:\demo\122016'


class MysqlConnection(object):

    """
        MYSQL数据库对象，负责产生数据库连接 , 此类中的连接采用连接池实现
        获取连接对象：conn = Mysql.getConn()
        释放连接对象;conn.close()或del conn
    """

    __pool = None

    def __init__(self):
        """
        数据库构造函数，从连接池中取出连接，并生成操作游标
       """
        self._conn = pymysql.connect(user=DB_USER, password=DB_PASSWORD,
                                     database=DB_DATABASE, host=DB_HOST,
                                     port=DB_PORT, charset=DB_CHARSET)
        self._cursor = self._conn.cursor()

    @staticmethod
    def __get_conn():
        """
        静态方法，从连接池中取出连接
        :return:pymysql.connection
        """
        if MysqlConnection.__pool is None:
            __pool = PooledDB(creator=pymysql, mincached=1, maxcached=20, user=DB_USER, password=DB_PASSWORD,
                              database=DB_DATABASE, host=DB_HOST, port=DB_PORT, charset=DB_CHARSET)
            return __pool.connection()

    def insert_all(self, sql):
        """
        向数据表插入多条记录
        :param self:
        :param sql:要插入的sql格式
        :param param:要插入的记录数据tuple(tuple)/list[list]
        :return:count受影响的行数
        """
        count = self._cursor.execute(sql)
        return count

    def begin(self):
        """
        @summary: 开启事务
        """
        self._conn.autocommit(0)

    def end(self, option='commit'):
        """
        @summary: 结束事务
        """
        if option == 'commit':
            self._conn.commit()
        else:
            self._conn.rollback()

    def dispose(self, is_end=1):
        """
        @summary: 释放连接池资源
        """
        if is_end == 1:
            self.end('commit')
        else:
            self.end('rollback')
        self._cursor.close()
        self._conn.close()


